use warnings;
use strict;
use Test::More qw(no_plan);
use ReefStats::Schema::ReefStatsDB;
use DateTime;

my $schema = ReefStats::Schema::ReefStatsDB->connect_for_testing;

isa_ok( $schema, 'ReefStats::Schema::ReefStatsDB' );

my $ara = $schema->resultset('ActionsReasonsAll');

#$ara->all();

$ara->search(
    undef,
    {
        select   => [ 'reason', { sum => 'number' } ],
        as       => [qw(reason number)],
        group_by => [qw(reason)],
        order_by => ['2 desc'],
    }
)->first;

__END__

my $upper_date = DateTime->now()->set_time_zone('local')->subtract( days => 1 );
my $lower_date = $upper_date->clone->subtract( days => 7 );
$upper_date->set_hour(23)->set_minute(59)->set_second(59)
  ->set_nanosecond(999999999);
$lower_date->set_hour(0)->set_minute(0)->set_second(0)->set_nanosecond(0);

my $search_criteria = { day => { -between => [ $lower_date, $upper_date ] }, };
my $case_logic = "case
                when action in ('Allowed', 'Whitelisted') then action 
                when action in ('Blocked', 'Deferred') then 'Blocked'
                when action in ('Quarantined', 'Per-User Quarantine') then 'Quarantined'
                else 'Other' 
            end";
my $rs = $maillog->search(
    {%$search_criteria},
    {
        select => [
            'day',
            {
                sum =>
"case when action in ('Allowed', 'Tagged') then number else 0 end"
            },
            {
                sum =>
"case when action in ('Blocked', 'Deferred') then number else 0 end"
            },
            {
                sum =>
"case when action in ('Quarantined', 'Per-User Quarantine') then number else 0 end"
            },
            {
                sum =>
                  "case when action in ('Whitelisted') then number else 0 end"
            }
        ],
        as       => [qw( day allowed blocked quarantined whitelisted )],
        group_by => [qw( day )],
        order_by => [qw( day )],
    }
);

while ( my $r = $rs->next ) {
    diag join( ' ',
        $r->cluster, $r->day,
        $r->get_column('action'),
        $r->get_column('action_count') );
}

__END__

my $case_logic = "case
                when action in ('Allowed', 'Whitelisted') then action 
                when action in ('Blocked', 'Deferred') then 'Blocked'
                when action in ('Quarantined', 'Per-User Quarantine') then 'Quarantined'
                else 'Other' 
            end";
my $rs = $maillog->search(
    {%$search_criteria},
    {
        select => [ 'cluster', 'day', $case_logic, { sum => 'number' } ],
        as       => [qw( cluster day action action_count )],
        group_by => [ qw( cluster day ), $case_logic ],
        order_by => [qw( cluster day action )],
    }
);

while ( my $r = $rs->next ) {
    diag join( ' ',
        $r->cluster, $r->day,
        $r->get_column('action'),
        $r->get_column('action_count') );
}

__END__

my $lower_date = DateTime->new(
    year   => 2008,
    month  => 1,
    day    => 15,
    hour   => 0,
    minute => 0,
    second => 0,
);
my $upper_date = DateTime->new(
    year   => 2008,
    month  => 1,
    day    => 15,
    hour   => 23,
    minute => 59,
    second => 59,
);

my $rs = $maillog->search(
    {},
    {
        select => [
            "case when reason = 'Virus' then 'Blackout' else reason end",
            { count => 'reason' }
        ],
        as => [ 'reason', 'reason_count' ],
        group_by =>
          ["case when reason = 'Virus' then 'Blackout' else reason end"],
    }
);

while( my $r = $rs->next ) {
    print $rs->get_column('reason'), ' ', $rs->get_column('reason_count'), "\n";
}

__END__

my $rs = $maillog->search(
    {
        reason    => 'Virus',
        timestamp => { -between => [ $lower_date, $upper_date ] },
        domain    => 'mercatorgroup.com'
    },
    {
        select   => [ 'reasonext', { count => 'reasonext' } ],
        as       => [qw( reasonext reasonext_count )],
        group_by => [qw( reasonext )],
        order_by => 'count(reasonext) desc',
        rows     => 10,
    }
);

for my $r ( $rs->next ) {
    diag $r;
}
#diag( $result->reasonext );
#diag( $result->get_column('reasonext_count') );

$schema->txn_rollback();

__END__

my $domains = $schema->resultset('Domains');
$domains->delete_all();
is( $domains->count(), 0, 'starting out with an empty domain set' );

my $nakedcode = $domains->create( { domain => 'www.nakedcode.com', } );

is( $nakedcode->domain, 'www.nakedcode.com', 'got the right domain back' );
like( $nakedcode->id, qr/^\d+$/, 'the id looks good' );
is( $domains->count(), 1, 'got a domain now' );

my $perlcast = $domains->create( { domain => 'www.perlcast.com', } );

is( $perlcast->domain, 'www.perlcast.com', 'got the right domain back' );
like( $perlcast->id, qr/^\d+$/, 'the id looks good' );
is( $domains->count(), 2, 'two domains' );

is( $domains->search( { domain => 'www.perlcast.com' } )->first->id,
    $perlcast->id, 'found the domain that we were looking for' );
$schema->txn_rollback();
